package demo;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.fasterxml.jackson.annotation.JsonTypeInfo.Id;
import com.voucher.crab2died.ExcelUtils;
import com.voucher.manage.singleton.*;
import com.voucher.manage.tools.CopyFile;
import com.voucher.manage.tools.MyTestUtil;

class DBUtils {
    private static Properties prop = new Properties();
    //static String url = "jdbc:jtds:sqlserver://223.86.150.188:1433/";
    //static String dataBase = "manage";
    static String url;
    static String dataBase;

    static {
        ClassLoader loader = DBUtils.class.getClassLoader();
        InputStream in = loader.getResourceAsStream("jdbc.properties");
        try {
            prop.load(in);
            Class.forName(prop.getProperty("driverClassName"));
            url = prop.getProperty("url");
            dataBase = url.substring(url.lastIndexOf("/") + 1);
            System.out.println("url======"+url+"           dataBase="+dataBase);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static Connection getConnection(String url,String username,String password) throws Exception {
    	if(username==null||username.equals(""))
    		username=prop.getProperty("username");
    	if(password==null||password.equals(""))
    		password=prop.getProperty("password");
        return DriverManager.getConnection(url, username,password);
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

}

@CrossOrigin(origins = "*")
@Controller
@RequestMapping("/excel")
public class ExcelController {

	static String pathRoot = System.getProperty("user.home");
	
	public final static String filePath=pathRoot+"\\Desktop\\pasoft\\excel\\sm\\";

      
    @RequestMapping("/put")
    public @ResponseBody Integer getExcelLabel(@RequestParam("file") MultipartFile file,
    		HttpServletRequest request,HttpServletResponse response) {

    	response.setHeader("Access-Control-Allow-Origin", "*");
    	   	
    	List<String> list=new ArrayList<>();

    	
		LinkedHashMap<String, Map<String,Object>> linkedHashMap=Singleton.getInstance().getRegisterMapLong();
		
		Map<String,Object> linkedMap=linkedHashMap.get("cookie");
		
		Map<String,Object> map=new HashMap<>();
		
		if(linkedMap!=null)
			map=linkedMap;
		
		map.put("startTime", new Date());
		
		System.out.println("filename="+file.getName());
		
		MyTestUtil.print(map);
		
		Map<String,Object> xlsMap;
		
		File targetFile=new File(filePath+file.getName());
		
		if(!targetFile.exists()){
            targetFile.mkdirs();//鍒涘缓鐩綍
            System.out.println("鐩綍涓嶅瓨鍦�");
        }
        
        try {
        	file.transferTo(targetFile);
        }catch (Exception e) {
			// TODO: handle exception
        	e.printStackTrace();
		} 
		
		if(map.get("xlsMap")!=null) {
			xlsMap=(Map) map.get("xlsMap");
		}else {
			xlsMap=new HashMap();
		}
        try {
        	 // @RequestParam("file") MultipartFile file 鏄敤鏉ユ帴鏀跺墠绔紶閫掕繃鏉ョ殑鏂囦欢
            // 1.鍒涘缓workbook瀵硅薄锛岃鍙栨暣涓枃妗�
            InputStream inputStream = new FileInputStream(new File(filePath+file.getName()));
            InputStreamReader read = new InputStreamReader(inputStream,"GBK");
            BufferedReader bufferedReader = new BufferedReader(read);
            String lineTxt = null;
            
            List xlsList=new ArrayList<>();
            String xx="";
			while ((lineTxt = bufferedReader.readLine()) != null) {
				System.out.println("lineTex="+lineTxt);

				String data[] = lineTxt.split("\t"); // 鏈枃浠朵腑鎸夊埗琛ㄧ鍒嗗壊
				int i=0;
				List list2=new ArrayList<>();
				for (String x : data) {
					System.out.println(x);
					if(i==0) {
						if(xlsMap.get(x)!=null) {
							xlsList=(List) xlsMap.get(x);
						}else {
							xlsList=new ArrayList<>();
							xlsMap.put(x, new ArrayList<>());
						}
						xx=x;
					}
					list2.add(x);
					i++;
				}
				xlsList.add(list2);

				xlsMap.put(xx, xlsList);
			}

			inputStream.close();
			read.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        map.put("xlsMap", xlsMap);
        
        linkedHashMap.put("cookie", map);
        
        int num=0;
        
        for (Map.Entry<String,Object> entry : xlsMap.entrySet()) {
        	List list2=(List) entry.getValue();
        	num+=list2.size();
        }
        
        //MyTestUtil.print(list);
        return num;
    }

    @RequestMapping("/get")
    public @ResponseBody List getxlsByorder(HttpServletRequest request,HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
    	
    	response.setHeader("Access-Control-Allow-Origin", "*");
    	
    	response.setHeader("Access-Control-Allow-Methods", "POST, GET, DELETE, OPTIONS, DELETE");
        // 响应头设置
    	response.setHeader("Access-Control-Allow-Headers", "Content-Type, x-requested-with, X-Custom-Header, HaiYi-Access-Token");

    	
    	List retList=new ArrayList<>();

		String pathRoot = System.getProperty("user.home");
		
		String filePath=pathRoot+Singleton.filePath;
		
		
		LinkedHashMap<String, Map<String,Object>> linkedHashMap=Singleton.getInstance().getRegisterMapLong();
		
		Map<String,Object> linkedMap=linkedHashMap.get("cookie");
		
		System.out.println("linkedMap=");
		////MyTestUtil.print(linkedMap);
		Map<String,Object> map0=new HashMap<>();
		
		if(linkedMap!=null)
			map0=linkedMap;
		
		Map<String,Object> xlsMap = (Map) map0.get("xlsMap");
		
		//System.out.println("xlsMap===");
		
		//MyTestUtil.print(xlsMap);
		
		Map exlceMap=new HashMap();
		
		try {
	       	 // @RequestParam("file") MultipartFile file 鏄敤鏉ユ帴鏀跺墠绔紶閫掕繃鏉ョ殑鏂囦欢
	           // 1.鍒涘缓workbook瀵硅薄锛岃鍙栨暣涓枃妗�
	           InputStream inputStream = new FileInputStream(new File(filePath+"//sm.xlsx"));
	           Workbook wb = WorkbookFactory.create(inputStream);
	           Sheet sheet = wb.getSheetAt(0);
	   			int end=sheet.getLastRowNum();
	           // 涓嶅熀浜庢敞瑙�,灏咵xcel鍐呭璇昏嚦List<List<String>>瀵硅薄鍐�
	           List<List<String>> lists = ExcelUtils.getInstance().readExcel2(wb, 0, end, 0);
	           System.out.println("璇诲彇Excel鑷砈tring鏁扮粍锛�");
	           for (List<String> l : lists) {
	        	   List list=new ArrayList<>();
	               for(String n:l){
	               		System.out.println("n="+n);
	               		list.add(n);
	               }
	               exlceMap.put(list.get(0), list.get(2));
	           }

	           System.out.println("exlceMap=");
	           MyTestUtil.print(exlceMap);
	           
	           inputStream.close();
	           
	       } catch (Exception e) {
	           e.printStackTrace();
	       }
			
		
		
        String nameString="xlsfile";
		
		String imgPath=request.getSession().getServletContext().getRealPath(Singleton.filePath);
		
		System.out.println("imgpath="+imgPath);
		
		Map map =new HashedMap();
		
        ZipOutputStream zos = null;
        
        OutputStream out;
        
		try {
			File targetFile=new File(imgPath);
			 if(!targetFile.exists()){
		            targetFile.mkdirs();//鍒涘缓鐩綍
		            System.out.println("鐩綍涓嶅瓨鍦�");
		        }
			File outFile=new File(imgPath+"\\"+nameString+".zip");
			out = new FileOutputStream(outFile);
			zos = new ZipOutputStream(out);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}   
        
		
		Map map2=new HashedMap();
		
		int n=0;
		
		List allList=new ArrayList();
		
		 for (Map.Entry<String,Object> entry : xlsMap.entrySet()) {
	            System.out.println("key = " + entry.getKey() + ", value = " + entry.getValue());
	            List list=(List) entry.getValue();
	            
	           
	            
	            List list2=new ArrayList<>();
	            
	           
                List<String> fields=new ArrayList<>();
	            
	            Iterator iterator=list.iterator();
	            
	            while (iterator.hasNext()) {
					
	            	List list3=(List) iterator.next();
	            	try {
	            	String number=(String) list3.get(1);
					
	            	String name=(String) exlceMap.get(number);
	            	if(name!=null) {
	            		if(list3.size()<4)
	            			list3.add(name);
	            	}
	            	//list2.add(list3);
	            	 allList.add(list3);
	            	}catch (Exception e) {
						// TODO: handle exception
					}
				}
	            
	          
    			
    			/*  try {
    				  System.out.println("imgPath="+imgPath);
    		            File file = new File(imgPath+"\\"+"xlsfile.zip");
    		            //System.out.println("sourceFile="+file);
    		            CopyFile.compress(file, zos, tableName+".xlsx", false);
    		        } catch (Exception e) {
    		            throw new RuntimeException("zip error from ZipUtils", e);
    		        } finally {
    		            if (zos != null) {
    		                try {
    		                    zos.close();
    		                } catch (IOException e) {
    		                    e.printStackTrace();
    		                }
    		            }
    		        }*/
    			
    			}
		 
    			//map.put(img.getOrder_num(), Singleton.filePath+"\\"+img.getImgUrl());
    	
		 String tableName="data";
		 
		 List<String> headers = new ArrayList<>();
		 
		 ExcelUtils.getInstance().exportObjects2Excel(allList, headers, filePath+tableName+".xlsx");

         CopyFile.setFile(imgPath, filePath+tableName+".xlsx", tableName+".xlsx");
     	
			//map2.put(img.getOrder_num(),1);
			
			String oldFile=filePath+"\\"+n+".xlsx";
			
			System.out.println("oldFile="+oldFile);
			
			CopyFile.set(imgPath+"\\"+tableName, oldFile, tableName+".xlsx");
			
			retList.add(tableName+".xlsx");
		 
		//return nameString+".zip";
		 
		 return retList;
		 
	}
    
    
    @RequestMapping("/clear")
    public void clear(HttpServletRequest request,HttpServletResponse response) throws IOException {
    	
    	LinkedHashMap<String, Map<String,Object>> linkedHashMap=Singleton.getInstance().getRegisterMapLong();

    	Map map=new HashMap();
    	Map xlsMap=new HashMap();
    	
    	map.put("xlsMap", xlsMap);
    	map.put("startTime", new Date());
    	linkedHashMap.put("cookie", map);
    	
    }
    
    @RequestMapping("/select")
    public @ResponseBody Integer select(HttpServletRequest request,HttpServletResponse response) throws IOException {
    	
    	LinkedHashMap<String, Map<String,Object>> linkedHashMap=Singleton.getInstance().getRegisterMapLong();
		
		Map<String,Object> linkedMap=linkedHashMap.get("cookie");
		
		Map<String,Object> map=new HashMap<>();
		
		if(linkedMap!=null)
			map=linkedMap;
    	
		int num=0;
        
		Map<String,Object> xlsMap=(Map<String, Object>) map.get("xlsMap");
		if(xlsMap!=null&&xlsMap.size()>0) {
        for (Map.Entry<String,Object> entry : xlsMap.entrySet()) {
        	List list2=(List) entry.getValue();
        	num+=list2.size();
        }
		}
        //MyTestUtil.print(list);
        return num;
    }
    
}
